// 引入了express模块
let express = require("express")();
let url = require('url');
let fs = require('fs');
let mysql = require('mysql');
let expressStatic = require('express-static');
const port = 80;

// 数据库的配置，做计划
var sql = mysql.createConnection({
	// 别改 
	host     : 'localhost',
	user     : 'root',
	password : '123456',
	database : 'school',
	timezone : "08:00"
});

// 尝试链接
sql.connect();

let count = 0; //客户端请求的次数

// 配置首页文件
express.get("/",(request,response)=>{
	fs.readFile("static/index.html",(error,data)=>{
		if(error){
			response.end("error");
			console.log("找不到首页文件了")
		}
		else{
			response.end(data);
		}
	})
})

// 计划一个服务于客户端的接口
express.get("/getData",(request,response)=>{

	// 查库
	sql.query(`SELECT * FROM students`,(error,data)=>{
		if(error){
			response.end("database error");
			console.log("数据库查找失败了")
		}
		else{
			// 反解析后向前端返回JSON字符串
			response.end(JSON.stringify(data))
		}
	})
})

// 计划一个服务于客户端的接口
express.get("/addData",(request,response)=>{

	// 返回本次来自前端请求附带参数列表的对象形式
	let name = url.parse(request.url,true).query.name;
	let sex = url.parse(request.url,true).query.sex;
	let age = url.parse(request.url,true).query.age;
	let city = url.parse(request.url,true).query.city;

	// 查库
	sql.query(`INSERT INTO students (name,age,sex,city) VALUES ("${name}","${age}","${sex}","${city}") `,(error,data)=>{
		if(error){
			response.end("database error");
			console.log(`添加${name}数据失败了`)
		}
		else{
			// 反解析后向前端返回JSON字符串
			response.end("success");
		}
	})
})

// 删除学员
express.get("/delete",(request,response)=>{

	let id = url.parse(request.url,true).query.id;

	if(id === undefined){
		response.end("请发送要删除的学员id")
	}
	else{

		sql.query(`DELETE FROM students WHERE id=${id}`,(error,data)=>{
			if(error){
				response.end("database error");
				console.log("数据库删除失败了")
			}
			else{
				// 反解析后向前端返回JSON字符串
				response.end(`success`)
			}
		})
	}
})
// 注册用户
express.get("/register",(request,response)=>{

	let username = url.parse(request.url,true).query.username;
	let password = url.parse(request.url,true).query.password;


	if(username && password){

		sql.query(`SELECT * FROM users WHERE username="${username}"`,(error,data)=>{
			if(data.length){
				response.end(`repeat`);
			}
			else{
				// 写入新用户数据
				sql.query(`INSERT INTO users (username,password) VALUES ("${username}","${password}")`,(error,data)=>{
					if(error){
						response.end("database error");
						console.log(error)
					}
					else{
						// 反解析后向前端返回JSON字符串
						response.end(`success`)
					}
				})
			}

		})

	}

	else{
		response.end("error")
	}
})
// 登录
express.get("/login",(request,response)=>{

	let username = url.parse(request.url,true).query.username;
	let password = url.parse(request.url,true).query.password;


	if(username && password){

		sql.query(`SELECT * FROM users WHERE username="${username}" AND password="${password}"`,(error,data)=>{
			if(data.length){
				response.end(String(data[0].username))
				console.log(`用户：${data[0].username}登录成功。`)
			}
			else{
				response.end("error")
			}
		})

	}

	else{
		response.end("error")
	}
})


// 查询分数接口
express.get("/getScore",(request,response)=>{
	let studentId = url.parse(request.url,true).query.id;
	let name = url.parse(request.url,true).query.name;
	let date = url.parse(request.url,true).query.date;


	if(name && date){
		sql.query(`select * from students,score WHERE students.id=score.studentId AND students.name="${name}" AND score.date="${date}"`,(error,data)=>{
			response.end(JSON.stringify(data))
		})
	}
	if(name){
		sql.query(`select * from students,score WHERE students.id=score.studentId AND students.name="${name}"`,(error,data)=>{
			response.end(JSON.stringify(data))
		})
	}
	if(studentId){
		sql.query(`select * from students,score WHERE students.id=score.studentId AND students.id=${studentId}`,(error,data)=>{
			response.end(JSON.stringify(data))
		})
	}
	if(!studentId && !name){
		sql.query(`select * from students,score WHERE students.id=score.studentId ORDER BY students.id`,(error,data)=>{
			response.end(JSON.stringify(data))
		})
	}
	
})



// 如果接收到来自前端的静态请求了，我应该去哪里（文件夹）找对应文件
express.use(expressStatic(`${__dirname}/static`))


// 开始监听来自前端的请求，也叫启动服务
// 这个操作不会停止
express.listen(port,()=>{
	console.log(`server is running at ${port}`)
})